package data;

import database.DataBaseConnection;
import entity.*;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import utility.Utility;

public class QueryRecordData {
    private String start;
    private String end;
    private String operator;
    private int pageNumber;
    private int pageSize;

    public QueryRecordData(String start, String end, String operator, int pageNumber, int pageSize) {
        this.start = start;
        this.end = end;
        this.operator = operator;
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
    }

    private String queryRecordData(String table) {
        ArrayList<Parent> parents = new ArrayList<>();
        Connection connection = DataBaseConnection.getConnection();
        String total, query;
        PreparedStatement totalPreparedStatement, queryPreparedStatement;
        JSONObject jsonObject = new JSONObject();
        if (connection != null) {
            if (this.operator.equals("")) {
                total = "SELECT count(*) AS count FROM " + table + " WHERE execute_time < ? AND execute_time > ?";
                query = "SELECT * FROM " + table + " WHERE execute_time < ? AND execute_time > ? " +
                        "ORDER BY id DESC LIMIT " + this.pageNumber + "," + this.pageSize;
            } else {
                total = "SELECT count(*) AS count FROM " + table + " WHERE execute_time < ? " +
                        "AND execute_time > ? AND operator = ?";
                query = "SELECT * FROM " + table + " WHERE execute_time < ? AND execute_time > ? " +
                        "AND operator = ? ORDER BY id DESC LIMIT " + this.pageNumber + "," + this.pageSize;
            }
            try {
                totalPreparedStatement = connection.prepareStatement(total);
                queryPreparedStatement = connection.prepareStatement(query);
                totalPreparedStatement.setString(1, this.end);
                totalPreparedStatement.setString(2, this.start);
                queryPreparedStatement.setString(1, this.end);
                queryPreparedStatement.setString(2, this.start);
                if (!this.operator.equals("")) {
                    totalPreparedStatement.setString(3, this.operator);
                    queryPreparedStatement.setString(3, this.operator);
                }
                int totalNumber = 0;
                ResultSet resultSet1 = totalPreparedStatement.executeQuery();
                while (resultSet1 != null && resultSet1.next()) {
                    totalNumber = resultSet1.getInt("count");
                }
                ResultSet resultSet2 = queryPreparedStatement.executeQuery();
                while (resultSet2 != null && resultSet2.next()) {
                    long id = resultSet2.getLong("id");
                    String algorithm = resultSet2.getString("algorithm");
                    Timestamp executeTime = resultSet2.getTimestamp("execute_time");
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = simpleDateFormat.format(executeTime);
                    String operator = resultSet2.getString("operator");
                    String provider = resultSet2.getString("provider");
                    String mode = resultSet2.getString("mode");
                    String padding = resultSet2.getString("padding");
                    String iv = resultSet2.getString("iv");
                    String secretKey = resultSet2.getString("secret_key");
                    Blob plainBlob = resultSet2.getBlob("plaintext");
                    byte[] plainText = plainBlob.getBytes(1,
                            Math.min(Utility.byteNumber, (int) plainBlob.length()));
                    Blob cipherBlob = resultSet2.getBlob("ciphertext");
                    byte[] cipherText = cipherBlob.getBytes(1,
                            Math.min(Utility.byteNumber, (int) cipherBlob.length()));
                    parents.add(new Parent(id, algorithm, time, operator, provider, mode, padding, iv,
                            secretKey, plainText, cipherText));
                }
                JSONArray jsonArray = JSONArray.fromObject(parents);
                jsonObject.put("total", totalNumber);
                jsonObject.put("rows", jsonArray);
                DataBaseConnection.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jsonObject.toString();
    }

    public String queryEncryptionData() {
        return this.queryRecordData("encryption");
    }

    public String queryDecryptionData() {
        return this.queryRecordData("decryption");
    }

    public String queryAuthenticatedEncryptionData() {
        return this.queryRecordData("authenticated_encryption");
    }

    public String querySignatureData() {
        ArrayList<Signature> signatures = new ArrayList<>();
        Connection connection = DataBaseConnection.getConnection();
        String total, query;
        PreparedStatement totalPreparedStatement, queryPreparedStatement;
        JSONObject jsonObject = new JSONObject();
        if (connection != null) {
            if (this.operator.equals("")) {
                total = "SELECT count(*) AS count FROM signature WHERE execute_time < ? AND execute_time > ?";
                query = "SELECT * FROM signature WHERE execute_time < ? AND execute_time > ? " +
                        "ORDER BY id DESC LIMIT "
                        + this.pageNumber + "," + this.pageSize;
            } else {
                total = "SELECT count(*) AS count FROM signature WHERE execute_time < ? " +
                        "AND execute_time > ? AND operator = ?";
                query = "SELECT * FROM signature WHERE execute_time < ? AND execute_time > ? " +
                        "AND operator = ? ORDER BY id DESC "
                        + "LIMIT " + this.pageNumber + "," + this.pageSize;
            }
            try {
                totalPreparedStatement = connection.prepareStatement(total);
                queryPreparedStatement = connection.prepareStatement(query);
                totalPreparedStatement.setString(1, this.end);
                totalPreparedStatement.setString(2, this.start);
                queryPreparedStatement.setString(1, this.end);
                queryPreparedStatement.setString(2, this.start);
                if (!this.operator.equals("")) {
                    totalPreparedStatement.setString(3, this.operator);
                    queryPreparedStatement.setString(3, this.operator);
                }
                ResultSet resultSet1 = totalPreparedStatement.executeQuery();
                int totalNumber = 0;
                while (resultSet1 != null && resultSet1.next()) {
                    totalNumber = resultSet1.getInt("count");
                }
                ResultSet resultSet2 = queryPreparedStatement.executeQuery();
                while (resultSet2 != null && resultSet2.next()) {
                    long id = resultSet2.getLong("id");
                    String algorithm = resultSet2.getString("algorithm");
                    Timestamp executeTime = resultSet2.getTimestamp("execute_time");
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = simpleDateFormat.format(executeTime);
                    String operator = resultSet2.getString("operator");
                    String provider = resultSet2.getString("provider");
                    String privateKey = resultSet2.getString("private_key");
                    Blob messageBlob = resultSet2.getBlob("message");
                    byte[] message = messageBlob.getBytes(1,
                            Math.min(Utility.byteNumber, (int) messageBlob.length()));
                    String signature = resultSet2.getString("signature");
                    signatures.add(new Signature(id, algorithm, time,
                            operator, provider, privateKey, message, signature));
                }
                JSONArray jsonArray = JSONArray.fromObject(signatures);
                jsonObject.put("total", totalNumber);
                jsonObject.put("rows", jsonArray);
                DataBaseConnection.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jsonObject.toString();
    }

    public String queryVerificationData() {
        ArrayList<Verification> verifications = new ArrayList<>();
        Connection connection = DataBaseConnection.getConnection();
        String total, query;
        PreparedStatement totalPreparedStatement, queryPreparedStatement;
        JSONObject jsonObject = new JSONObject();
        if (connection != null) {
            if (this.operator.equals("")) {
                total = "SELECT count(*) AS count FROM verification WHERE execute_time < ? AND execute_time > ?";
                query = "SELECT * FROM verification WHERE execute_time < ? AND execute_time > ? ORDER BY id DESC LIMIT " +
                        this.pageNumber + "," + this.pageSize;
            } else {
                total = "SELECT count(*) AS count FROM verification WHERE execute_time < ? AND execute_time > ? AND operator = ?";
                query = "SELECT * FROM verification WHERE execute_time < ? AND execute_time > ? AND operator = ? " +
                        "ORDER BY id DESC LIMIT " + this.pageNumber + "," + this.pageSize;
            }
            try {
                totalPreparedStatement = connection.prepareStatement(total);
                queryPreparedStatement = connection.prepareStatement(query);
                totalPreparedStatement.setString(1, this.end);
                totalPreparedStatement.setString(2, this.start);
                queryPreparedStatement.setString(1, this.end);
                queryPreparedStatement.setString(2, this.start);
                if (!this.operator.equals("")) {
                    totalPreparedStatement.setString(3, this.operator);
                    queryPreparedStatement.setString(3, this.operator);
                }
                ResultSet result = totalPreparedStatement.executeQuery();
                int totalNumber = 0;
                while (result != null && result.next()) {
                    totalNumber = result.getInt("count");
                }
                ResultSet resultSet = queryPreparedStatement.executeQuery();
                while (resultSet != null && resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String algorithm = resultSet.getString("algorithm");
                    Timestamp executeTime = resultSet.getTimestamp("execute_time");
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = simpleDateFormat.format(executeTime);
                    String operator = resultSet.getString("operator");
                    String provider = resultSet.getString("provider");
                    String publicKey = resultSet.getString("public_key");
                    Blob messageBlob = resultSet.getBlob("message");
                    byte[] message = messageBlob.getBytes(1, Math.min(Utility.byteNumber, (int) messageBlob.length()));
                    String signature = resultSet.getString("signature");
                    String resultInfo = resultSet.getBoolean("result") ? "成功" : "失败";
                    verifications.add(new Verification(id, algorithm, time,
                            operator, provider, publicKey, message, signature, resultInfo));
                }
                JSONArray jsonArray = JSONArray.fromObject(verifications);
                jsonObject.put("total", totalNumber);
                jsonObject.put("rows", jsonArray);
                DataBaseConnection.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jsonObject.toString();
    }

    public String querySigncryptionData() {
        ArrayList<Signcryption> signcryptions = new ArrayList<>();
        Connection connection = DataBaseConnection.getConnection();
        String total, query;
        PreparedStatement totalPreparedStatement, queryPreparedStatement;
        JSONObject jsonObject = new JSONObject();
        if (connection != null) {
            if (this.operator.equals("")) {
                total = "SELECT count(*) AS count FROM signcryption WHERE execute_time < ? AND execute_time > ?";
                query = "SELECT * FROM signcryption WHERE execute_time < ? AND execute_time > ? ORDER BY id DESC LIMIT "
                        + this.pageNumber + "," + this.pageSize;
            } else {
                total = "SELECT count(*) AS count FROM signcryption WHERE execute_time < ? AND execute_time > ? AND operator = ?";
                query = "SELECT * FROM signcryption WHERE execute_time < ? AND execute_time > ? AND operator = ? " +
                        "ORDER BY id DESC LIMIT " + this.pageNumber + "," + this.pageSize;
            }
            try {
                totalPreparedStatement = connection.prepareStatement(total);
                queryPreparedStatement = connection.prepareStatement(query);
                totalPreparedStatement.setString(1, this.end);
                totalPreparedStatement.setString(2, this.start);
                queryPreparedStatement.setString(1, this.end);
                queryPreparedStatement.setString(2, this.start);
                if (!this.operator.equals("")) {
                    totalPreparedStatement.setString(3, this.operator);
                    queryPreparedStatement.setString(3, this.operator);
                }
                ResultSet result = totalPreparedStatement.executeQuery();
                int totalNumber = 0;
                while (result != null && result.next()) {
                    totalNumber = result.getInt("count");
                }
                ResultSet resultSet = queryPreparedStatement.executeQuery();
                while (resultSet != null && resultSet.next()) {
                    long id = resultSet.getLong("id");
                    String algorithm = resultSet.getString("algorithm");
                    Timestamp executeTime = resultSet.getTimestamp("execute_time");
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = simpleDateFormat.format(executeTime);
                    String operator = resultSet.getString("operator");
                    String provider = resultSet.getString("provider");
                    String encryptKey = resultSet.getString("encrypt_key");
                    String signKey = resultSet.getString("sign_key");
                    Blob plainBlob = resultSet.getBlob("plaintext");
                    byte[] plainText = plainBlob.getBytes(1, Math.min(Utility.byteNumber, (int) plainBlob.length()));
                    Blob cipherBlob = resultSet.getBlob("ciphertext");
                    byte[] cipherText = cipherBlob.getBytes(1, Math.min(Utility.byteNumber, (int) cipherBlob.length()));
                    String signature = resultSet.getString("signature");
                    signcryptions.add(new Signcryption(id, algorithm, time,
                            operator, provider, encryptKey, signKey, plainText, cipherText, signature));
                }
                JSONArray jsonArray = JSONArray.fromObject(signcryptions);
                jsonObject.put("total", totalNumber);
                jsonObject.put("rows", jsonArray);
                DataBaseConnection.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jsonObject.toString();
    }

    public String queryUnsigncryptionData() {
        ArrayList<Unsigncryption> unsigncryptions = new ArrayList<>();
        Connection connection = DataBaseConnection.getConnection();
        String total, query;
        PreparedStatement totalPreparedStatement, queryPreparedStatement;
        JSONObject jsonObject = new JSONObject();
        if (connection != null) {
            if (this.operator.equals("")) {
                total = "SELECT count(*) AS count FROM unsigncryption WHERE execute_time < ? AND execute_time > ?";
                query = "SELECT * FROM unsigncryption WHERE execute_time < ? AND execute_time > ? ORDER BY id DESC LIMIT "
                        + this.pageNumber + "," + this.pageSize;
            } else {
                total = "SELECT count(*) AS count FROM unsigncryption WHERE execute_time < ? AND execute_time > ? AND operator = ?";
                query = "SELECT * FROM unsigncryption WHERE execute_time < ? AND execute_time > ? AND operator = ? " +
                        "ORDER BY id DESC LIMIT " + this.pageNumber + "," + this.pageSize;
            }
            try {
                totalPreparedStatement = connection.prepareStatement(total);
                queryPreparedStatement = connection.prepareStatement(query);
                totalPreparedStatement.setString(1, this.end);
                totalPreparedStatement.setString(2, this.start);
                queryPreparedStatement.setString(1, this.end);
                queryPreparedStatement.setString(2, this.start);
                if (!this.operator.equals("")) {
                    totalPreparedStatement.setString(3, this.operator);
                    queryPreparedStatement.setString(3, this.operator);
                }
                ResultSet result = totalPreparedStatement.executeQuery();
                int totalNumber = 0;
                while (result != null && result.next()) {
                    totalNumber = result.getInt("count");
                }
                ResultSet resultSet = queryPreparedStatement.executeQuery();
                while (resultSet != null && resultSet.next()) {
                    long id = resultSet.getLong("id");
                    String algorithm = resultSet.getString("algorithm");
                    Timestamp executeTime = resultSet.getTimestamp("execute_time");
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = simpleDateFormat.format(executeTime);
                    String operator = resultSet.getString("operator");
                    String provider = resultSet.getString("provider");
                    String decryptKey = resultSet.getString("decrypt_key");
                    String verifyKey = resultSet.getString("verify_key");
                    Blob plainBlob = resultSet.getBlob("plaintext");
                    byte[] plainText = plainBlob.getBytes(1, Math.min(Utility.byteNumber, (int) plainBlob.length()));
                    Blob cipherBlob = resultSet.getBlob("ciphertext");
                    byte[] cipherText = cipherBlob.getBytes(1, Math.min(Utility.byteNumber, (int) cipherBlob.length()));
                    String signature = resultSet.getString("signature");
                    String resultInfo = resultSet.getBoolean("result") ? "成功" : "失败";
                    unsigncryptions.add(new Unsigncryption(id, algorithm, time,
                            operator, provider, decryptKey, verifyKey, plainText, cipherText, signature, resultInfo));
                }
                JSONArray jsonArray = JSONArray.fromObject(unsigncryptions);
                jsonObject.put("total", totalNumber);
                jsonObject.put("rows", jsonArray);


                DataBaseConnection.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jsonObject.toString();
    }

    public String queryDecryptionVerificationData() {
        ArrayList<DecryptionVerification> decryptionVerifications = new ArrayList<>();
        Connection connection = DataBaseConnection.getConnection();
        String total, query;
        PreparedStatement totalPreparedStatement, queryPreparedStatement;
        JSONObject jsonObject = new JSONObject();
        if (connection != null) {
            if (this.operator.equals("")) {
                total = "SELECT count(*) AS count FROM decryption_verification WHERE execute_time < ? AND execute_time > ?";
                query = "SELECT * FROM decryption_verification WHERE execute_time < ? AND execute_time > ? " +
                        "ORDER BY id DESC LIMIT " + this.pageNumber + "," + this.pageSize;
            } else {
                total = "SELECT count(*) AS count FROM decryption_verification WHERE execute_time < ? AND execute_time > ? " +
                        "AND operator = ?";
                query = "SELECT * FROM decryption_verification WHERE execute_time < ? AND execute_time > ? AND operator = ? " +
                        "ORDER BY id DESC LIMIT " + this.pageNumber + "," + this.pageSize;
            }
            try {
                totalPreparedStatement = connection.prepareStatement(total);
                queryPreparedStatement = connection.prepareStatement(query);
                totalPreparedStatement.setString(1, this.end);
                totalPreparedStatement.setString(2, this.start);
                queryPreparedStatement.setString(1, this.end);
                queryPreparedStatement.setString(2, this.start);
                if (!this.operator.equals("")) {
                    totalPreparedStatement.setString(3, this.operator);
                    queryPreparedStatement.setString(3, this.operator);
                }
                ResultSet result = totalPreparedStatement.executeQuery();
                int totalNumber = 0;
                while (result != null && result.next()) {
                    totalNumber = result.getInt("count");
                }
                ResultSet resultSet = queryPreparedStatement.executeQuery();
                while (resultSet != null && resultSet.next()) {
                    long id = resultSet.getLong("id");
                    String algorithm = resultSet.getString("algorithm");
                    Timestamp executeTime = resultSet.getTimestamp("execute_time");
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = simpleDateFormat.format(executeTime);
                    String operator = resultSet.getString("operator");
                    String provider = resultSet.getString("provider");
                    String mode = resultSet.getString("mode");
                    String padding = resultSet.getString("padding");
                    String iv = resultSet.getString("iv");
                    String secretKey = resultSet.getString("secret_key");
                    Blob plainBlob = resultSet.getBlob("plaintext");
                    byte[] plainText = plainBlob.getBytes(1, Math.min(Utility.byteNumber, (int) plainBlob.length()));
                    Blob cipherBlob = resultSet.getBlob("ciphertext");
                    byte[] cipherText = cipherBlob.getBytes(1, Math.min(Utility.byteNumber, (int) cipherBlob.length()));
                    String resultInfo = resultSet.getBoolean("result") ? "成功" : "失败";
                    decryptionVerifications.add(new DecryptionVerification(id, algorithm, time, operator, provider, mode, padding,
                            iv, secretKey, plainText, cipherText, resultInfo));
                }
                JSONArray jsonArray = JSONArray.fromObject(decryptionVerifications);
                jsonObject.put("total", totalNumber);
                jsonObject.put("rows", jsonArray);
                DataBaseConnection.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jsonObject.toString();
    }

    public String queryMacData() {
        ArrayList<Mac> macArrayList = new ArrayList<>();
        Connection connection = DataBaseConnection.getConnection();
        String total, query;
        PreparedStatement totalPreparedStatement, queryPreparedStatement;
        JSONObject jsonObject = new JSONObject();
        if (connection != null) {
            if (this.operator.equals("")) {
                total = "SELECT count(*) AS count FROM mac WHERE execute_time < ? AND execute_time > ?";
                query = "SELECT * FROM mac WHERE execute_time < ? AND execute_time > ? ORDER BY id DESC LIMIT "
                        + this.pageNumber + "," + this.pageSize;
            } else {
                total = "SELECT count(*) AS count FROM mac WHERE execute_time < ? AND execute_time > ? AND operator = ?";
                query = "SELECT * FROM mac WHERE execute_time < ? AND execute_time > ? AND operator = ? " +
                        "ORDER BY id DESC LIMIT " + this.pageNumber + "," + this.pageSize;
            }
            try {
                totalPreparedStatement = connection.prepareStatement(total);
                queryPreparedStatement = connection.prepareStatement(query);
                totalPreparedStatement.setString(1, this.end);
                totalPreparedStatement.setString(2, this.start);
                queryPreparedStatement.setString(1, this.end);
                queryPreparedStatement.setString(2, this.start);
                if (!this.operator.equals("")) {
                    totalPreparedStatement.setString(3, this.operator);
                    queryPreparedStatement.setString(3, this.operator);
                }
                ResultSet result = totalPreparedStatement.executeQuery();
                int totalNumber = 0;
                while (result != null && result.next()) {
                    totalNumber = result.getInt("count");
                }
                ResultSet resultSet = queryPreparedStatement.executeQuery();
                while (resultSet != null && resultSet.next()) {
                    long id = resultSet.getLong("id");
                    String algorithm = resultSet.getString("algorithm");
                    Timestamp executeTime = resultSet.getTimestamp("execute_time");
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = simpleDateFormat.format(executeTime);
                    String operator = resultSet.getString("operator");
                    String provider = resultSet.getString("provider");
                    String secretKey = resultSet.getString("secret_key");
                    Blob message = resultSet.getBlob("message");
                    byte[] messageBytes = message.getBytes(1, Math.min(Utility.byteNumber, (int) message.length()));
                    String authenticationCode = resultSet.getString("authentication_code");
                    macArrayList.add(new Mac(id, algorithm, time, operator, provider, secretKey, messageBytes, authenticationCode));
                }
                JSONArray jsonArray = JSONArray.fromObject(macArrayList);
                jsonObject.put("total", totalNumber);
                jsonObject.put("rows", jsonArray);
                DataBaseConnection.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jsonObject.toString();
    }
}
